﻿'' Select Query Usage
'' 
'' ALWAYS USE ESCAPE WHERE REQUIRED
''
'' If 1 ROW required
''  Run Query with the Query Function (pass query string to it)
''  Then, either run the getrow with no params, or if you've ran another query, pass the returned whotsit from the query to it
''  eg.
''        MySQLManager.Query("SELECT * FROM userdata WHERE Username = 'test'")
''        Dim temp As String() = MySQLManager.GetRow()
''        temp(0) is the same as the 1st field in the db, temp (1) is the 2nd, etc
''
''
'' If Running INSERT, UPDATE, those types of querys, use BasicQuery


Module MySQLManager

    Public InternetConnection As Boolean = True
    Public ServerDown As Boolean = False

    Dim file As String = System.Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\WSI\Config\Repo.xml"
    Private Port As String = ReadXML.Read(file, "RepoConfig", "RepoPort")
    Private Host As String = ReadXML.Read(file, "RepoConfig", "RepoServer")
    Private User As String = ReadXML.Read(file, "RepoConfig", "RepoUser")
    Private Password As String = ReadXML.Read(file, "RepoConfig", "RepoPass")
    Private DB As String = ReadXML.Read(file, "RepoConfig", "RepoDatabase")

    Private LastQuery As Object = Nothing

    Public con As New MySqlConnection

    Public Function Connect() As Boolean
        Try
            con.ConnectionString = "Data Source=" & Host & ";Database=" & DB & ";Uid=" & User & ";Pwd=" & Password & ";"
            con.Open()
            MainWindow.DBimage.Image = My.Resources.database_link
        Catch ex As MySqlException
            'MsgBox(ex.ToString())
            InternetChecks.ServerDown()
            MainWindow.DBimage.Image = My.Resources.database_error
        End Try
        Return 0
    End Function

    Private da As MySqlDataAdapter
    Private conn As MySqlConnection
    Private bsource As BindingSource = New BindingSource()
    Private ds As DataSet = Nothing
    Private sql As String
    Private sql2 As String
    Private sqlfinal As String



    Sub LoadData(ByVal query As String, ByVal cat As String)
        Dim connectionString As String = "Data Source=" & Host & ";Database=" & DB & ";Uid=" & User & ";Pwd=" & Password & ";"
        conn = New MySqlConnection(connectionString)
        Dim srch As String
        srch = "%" & query & "%"
        If query = "" Then
            sql = "SELECT * FROM software"
        Else
            sql = "SELECT * FROM software WHERE Softwaretitle LIKE '" & srch & "'"
        End If
        If cat = "" Then
            sql2 = ""
        ElseIf query = "" And cat <> "" Then
            sql2 = " WHERE Category='" & cat & "'"
        Else
            sql2 = " AND Category='" & cat & "'"
        End If
        sqlfinal = sql & sql2
        da = New MySqlDataAdapter(sqlfinal, conn)
        conn.Open()
        ds = New DataSet()
        Dim commandBuilder As MySqlCommandBuilder = New MySqlCommandBuilder(da)
        Try
            da.Fill(ds, "software")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        bsource.DataSource = ds.Tables("software")
        'hide un-needed columns
        MainWindow.DataGridView1.DataSource = bsource
        MainWindow.DataGridView1.Columns(0).Visible = False
        MainWindow.DataGridView1.Columns(4).Visible = False
        MainWindow.DataGridView1.Columns(5).Visible = False
        MainWindow.DataGridView1.Columns(6).Visible = False
        'change widths
        MainWindow.DataGridView1.Columns(1).Width = 420
        MainWindow.DataGridView1.Columns(2).Width = 50
        MainWindow.DataGridView1.Columns(3).Width = 200
        'change Headers
        MainWindow.DataGridView1.Columns(1).HeaderText = "Title"
        MainWindow.DataGridView1.Columns(2).HeaderText = "Version"
        MainWindow.DataGridView1.Columns(3).HeaderText = "Publisher"


    End Sub

End Module
